﻿using System;
using System.Collections.Generic;
using System.Text;
using Pixysoft.Framework.XSchEmA.Entity;
using Pixysoft.Framework.XSchEmA.Sql;

namespace Pixysoft.Framework.XSchEmA.Database
{
    abstract class CommonDbSchemaUploader
    {
        protected ISchemaCommand command;

        protected ISqlBuilder sqlBuilder;

        public CommonDbSchemaUploader(ISchemaCommand command, ISqlBuilder builder)
        {
            this.command = command;

            this.sqlBuilder = builder;
        }

        public bool CreateSchema(DatabaseSchema schema)
        {
            if (schema == null)
                return false;

            string sql = null;

            List<string> tblist = new List<string>();

            foreach (TableSchema table in schema.Tables)
            {
                if (tblist.Contains(table.TableName))
                    continue;

                CreateTable(tblist, schema, table);
            }


            foreach (PrimaryKeySchema pk in schema.PrimaryKeys)
            {
                sql = sqlBuilder.AlterTableCreateConstraint(pk);

                //09-10-12 针对不支持的数据库 跳过

                if (string.IsNullOrEmpty(sql))
                    continue;

                command.SQL = sql;

                command.Execute();
            }

            foreach (UniqueSchema unique in schema.Uniques)
            {
                sql = sqlBuilder.AlterTableCreateConstraint(unique);

                //09-10-12 针对不支持的数据库 跳过

                if (string.IsNullOrEmpty(sql))
                    continue;

                command.SQL = sql;

                command.Execute();
            }

            foreach (IndexSchema index in schema.Indexs)
            {
                sql = sqlBuilder.AlterTableCreateIndex(index);

                //09-10-12 针对不支持的数据库 跳过

                if (string.IsNullOrEmpty(sql))
                    continue;

                command.SQL = sql;

                command.Execute();
            }


            foreach (ForeignKeySchema fk in schema.ForeignKeys)
            {
                sql = sqlBuilder.AlterTableCreateConstraint(fk);

                //09-10-12 针对不支持的数据库 跳过

                if (string.IsNullOrEmpty(sql))
                    continue;

                command.SQL = sql;

                command.Execute();
            }



            return true;

        }

        private bool CreateTable(List<string> tblist, DatabaseSchema schema, TableSchema table)
        {
            if (tblist.Contains(table.TableName))//已经创建了
                return true;

            tblist.Add(table.TableName);

            //根据外键关联去创建表

            List<ForeignKeySchema> fklist = schema.GetForeignKeys(table);

            foreach (ForeignKeySchema fk in fklist)
            {
                IConstraintSchema constraint = schema.GetConstraint(fk.PkeyConsName);

                if (constraint == null)
                    continue;

                CreateTable(tblist, schema, constraint.Table);
            }

            string sql = sqlBuilder.CreateTable(schema, table);

            command.SQL = sql;

            command.Execute();

            return true;

        }

        public bool ClearSchema(DatabaseSchema schema)
        {
            if (schema == null)
                return false;

            string sql = null;

            foreach (ForeignKeySchema fk in schema.ForeignKeys)
            {
                sql = sqlBuilder.AlterTableDropConstraint(fk);

                //09-10-12 针对不支持的数据库 跳过

                if (string.IsNullOrEmpty(sql))
                    return false;

                command.SQL = sql;

                try
                {
                    command.Execute();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex.ToString());

                    LoggerHelper.Error(sql);
                    //do nothing!
                }
            }

            foreach (UniqueSchema unique in schema.Uniques)
            {
                sql = sqlBuilder.AlterTableDropConstraint(unique);

                //09-10-12 针对不支持的数据库 跳过

                if (string.IsNullOrEmpty(sql))
                    return false;

                command.SQL = sql;

                try
                {
                    command.Execute();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex.ToString());

                    LoggerHelper.Error(sql);
                    //do nothing!
                }
            }

            foreach (PrimaryKeySchema pk in schema.PrimaryKeys)
            {
                sql = sqlBuilder.AlterTableDropConstraint(pk);

                //09-10-12 针对不支持的数据库 跳过

                if (string.IsNullOrEmpty(sql))
                    return false;

                command.SQL = sql;

                try
                {
                    command.Execute();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex.ToString());

                    LoggerHelper.Error(sql);
                    //do nothing!
                }
            }


            foreach (TableSchema table in schema.Tables)
            {
                sql = sqlBuilder.DropTable(table);

                command.SQL = sql;

                try
                {
                    command.Execute();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex.ToString());

                    LoggerHelper.Error(sql);

                    //do nothing!
                }
            }



            return true;
        }

        public bool CreateAutoIncrement(DatabaseSchema database)
        {
            foreach (TableSchema tbschema in database.Tables)
            {
                foreach (ColumnSchema colschema in tbschema.Columns)
                {
                    if (colschema.DataType.IsAutoIncrement)
                        CreateAutoIncrement(colschema);
                }
            }

            return true;
        }

        /// <summary>
        /// 针对mysql的自增主键问题
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public abstract bool CreateAutoIncrement(ColumnSchema column);
    }


    class OracleCommonSchemaUploader : CommonDbSchemaUploader
    {
        public OracleCommonSchemaUploader(ISchemaCommand command)
            : base(command, OracleSqlBuilder.Instance)
        {
        }

        public override bool CreateAutoIncrement(ColumnSchema column)
        {
            // do nothing
            return true;
        }
    }

    class AccessCommonSchemaUploader : CommonDbSchemaUploader
    {
        public AccessCommonSchemaUploader(ISchemaCommand command)
            :
            base(command, AccessSqlBuilder.Instance)
        {
        }

        public override bool CreateAutoIncrement(ColumnSchema column)
        {
            if (column == null)
                return false;

            string sql = null;

            sql = (sqlBuilder as AccessSqlBuilder).AlterTableModifyAutoIncColumn(column);

            command.SQL = sql;

            try
            {
                command.Execute();
            }
            catch (Exception ex)
            {
                LoggerHelper.Error(ex.ToString());

                return false;
            }

            return true;
        }
    }

    class MySqlCommonSchemaUploader : CommonDbSchemaUploader
    {
        public MySqlCommonSchemaUploader(ISchemaCommand command)
            : base(command, MySqlBuilder.Instance)
        {
        }

        public override bool CreateAutoIncrement(ColumnSchema column)
        {
            if (column == null)
                return false;

            string sql = null;

            sql = (sqlBuilder as MySqlBuilder).AlterTableModifyAutoIncColumn(column);

            command.SQL = sql;

            try
            {
                command.Execute();
            }
            catch (Exception ex)
            {
                LoggerHelper.Error(ex.ToString());

                return false;
            }

            return true;
        }
    }

    class SqlServerCommonSchemaUploader : CommonDbSchemaUploader
    {
        public SqlServerCommonSchemaUploader(ISchemaCommand command)
            : base(command, SqlServerSqlBuilder.Instance)
        {
        }

        public override bool CreateAutoIncrement(ColumnSchema column)
        {
            return true; //不允许后期修改
        }
    }

    class SQLiteCommonSchemaUploader : CommonDbSchemaUploader
    {
        public SQLiteCommonSchemaUploader(ISchemaCommand command)
            : base(command, SQLiteSqlBuilder.Instance)
        {
        }

        public override bool CreateAutoIncrement(ColumnSchema column)
        {
            return true; //不允许后期修改
        }
    }
}
